﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Trade.Model;

namespace Trade.DAL
{
    public class Purchase
    {
        public static string cmdPurchaseMasterString = "Select M.PurchaseNo, M.VendorNo, M.Dept, M.ContractName, M.ContractNo, M.ReceivedWH, M.Buyer, M.PayAccount, M.PayAmount, M.PayDate, M.InDate, M.EntryDate, M.EntryEmployee, M.InvoiceNo, M.InvoiceDate, M.Remark, M.RecordID from PurchaseMaster M where 1 > 2";
        public static string cmdPurchaseDetailString = "Select D.PurchaseNo, D.[LineNo], D.ProductNo,P.ProductName, P.ProductSpec, S.SubjectName, D.LotNo, D.ProductDate, D.Qty, D.Price, D.Amount, D.DiscountRate, D.DiscountPrice, D.DiscountAmt, D.TaxRate, D.TaxAmount, D.PriceInTax, D.AmountInTax, D.ValidDate, D.Remark, D.RecordId from PurchaseDetail D left join Product P on D.ProductNo = P.ProductNo left join SubjectMessage S on P.BaseUnit = S.SubjectId ";
        public static string cmdDetailCondition1 = "where 1 > 2";
        public static string cmdDetailCondition2 = "where D.PurchaseNo = @p_PurchaseNo";

        //创建临时数据库存放当前的PurchaseOrder的主资料
        public DataTable PurchaseMasterTemplate()
        {
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, cmdPurchaseMasterString, null))
            {
                return Utility.ConvertToDataTable(dr);
            }
        }

        //创建临时数据表存放当前的PurchaseOrder的明细资料行
        public DataTable PurchaseDetailTemplate()
        {
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, cmdPurchaseDetailString, null))
            {
                return Utility.ConvertToDataTable(dr);
            }
        }

        //根据查询条件读取相应的PurchaseOrder列表
        public DataTable GetPurchaseMaster(string purchaseNo, string referenceNo, string vendorNo, int warehouse, string buyer, DateTime entryDate, bool invoiceStatus)
        {
            string cmdString = "exec Usp_SelectPurchaseByCondition @p_PurchaseNo, @p_ReferenceNo, @p_VendorNo, @p_Warehouse, @p_Buyer, @p_EntryDate, @p_InvoiceStatus";

            SqlParameter[] parms = {
                new SqlParameter("@p_PurchaseNo", SqlDbType.VarChar, 30),
                new SqlParameter("@p_ReferenceNo", SqlDbType.VarChar, 30),
                new SqlParameter("@p_VendorNo", SqlDbType.VarChar, 8),
                new SqlParameter("@p_Warehouse", SqlDbType.Int),
                new SqlParameter("@p_Buyer", SqlDbType.VarChar, 20),
                new SqlParameter("@p_EntryDate", SqlDbType.DateTime),
                new SqlParameter("@p_InvoiceStatus", SqlDbType.Bit)};

            parms[0].Value = purchaseNo.Trim();
            parms[1].Value = referenceNo.Trim();
            parms[2].Value = vendorNo.Trim();
            parms[3].Value = warehouse;
            parms[4].Value = buyer.Trim();
            parms[5].Value = entryDate;
            parms[6].Value = (invoiceStatus == true ? 1 : 0);

            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, cmdString, parms))
            {
                return Utility.ConvertToDataTable(dr);
            }
        }

        //根据指定的PurchaseNo读取相应的PurchaseOrder的明细资料行
        public DataTable GetPurchaseDetailByNo(string purchaseNo)
        {
            SqlParameter parm = new SqlParameter("@p_PurchaseNo", SqlDbType.VarChar, 30);

            parm.Value = purchaseNo.Trim();

            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, cmdPurchaseDetailString + cmdDetailCondition2, parm))
            {
                return Utility.ConvertToDataTable(dr);
            }
        }

        //生成新的采购单编号
        public string GenPurchaseNo(string curDate)
        {
            string cmdString = "exec Usp_SerialNo @p_SerialNoName, @p_FieldName, @p_TableName, @p_PrefixTime, @p_RSerialNo out";
            string purchaseNo = "";

            SqlParameter[] parms = {
                new SqlParameter("@p_SerialNoName", SqlDbType.VarChar, 30),
                new SqlParameter("@p_FieldName", SqlDbType.VarChar, 30),
                new SqlParameter("@p_TableName", SqlDbType.VarChar, 30),
                new SqlParameter("@p_PrefixTime", SqlDbType.VarChar, 8),
                new SqlParameter("@p_RSerialNo", SqlDbType.VarChar, 30)};

            parms[0].Value = "SerialNo";
            parms[1].Value = "PurchaseNo";
            parms[2].Value = "PurchaseMaster";
            parms[3].Value = curDate.Trim();
            parms[4].Value = purchaseNo;
            parms[4].Direction = ParameterDirection.InputOutput;

            SQLHelper.ExecuteNonQuery(SQLHelper.connectionString, CommandType.Text, cmdString, parms);
            purchaseNo = parms[4].Value.ToString();

            return purchaseNo;
        }

        public void Modify(PurchaseMasterInfo purchaseMaster, DataTable dtPurchaseDetail)
        { 

        }

        private SqlParameter[] SetPurchaseMasterParameters(PurchaseMasterInfo purchaseMaster, string userId)
        {
            SqlParameter[] parms = {
                new SqlParameter("@PurchaseNo", SqlDbType.VarChar, 30),
                new SqlParameter("@VendorNo", SqlDbType.VarChar, 8), 
                new SqlParameter("@Dept", SqlDbType.Int),
                new SqlParameter("@ContractName", SqlDbType.VarChar, 30),
                new SqlParameter("@ContractNo", SqlDbType.VarChar, 30), 
                new SqlParameter("@ReceivedWh", SqlDbType.Int), 
                new SqlParameter("@Buyer", SqlDbType.VarChar, 20), 
                new SqlParameter("@PayAccount", SqlDbType.VarChar, 30), 
                new SqlParameter("@PayAmount", SqlDbType.Decimal, 15),
                new SqlParameter("@PayDate", SqlDbType.DateTime), 
                new SqlParameter("@InDate", SqlDbType.DateTime), 
                new SqlParameter("@EntryDate", SqlDbType.DateTime), 
                new SqlParameter("@EntryEmployee", SqlDbType.VarChar, 20),
                new SqlParameter("@InvoiceNo", SqlDbType.VarChar, 30),
                new SqlParameter("@InvoiceDate", SqlDbType.DateTime),
                new SqlParameter("@ReferenceNo", SqlDbType.VarChar, 30), 
                new SqlParameter("@Remark", SqlDbType.VarChar, 120),
                new SqlParameter("@RecordId", SqlDbType.Int),
                new SqlParameter("@UserId", SqlDbType.VarChar, 15), 
                new SqlParameter("@ErrMsg", SqlDbType.VarChar, 100),
                new SqlParameter("@RFlag", SqlDbType.SmallInt)};

            parms[0].Value = purchaseMaster.PurchaseNo.Trim();
            parms[1].Value = purchaseMaster.VendorNo.Trim();
            parms[2].Value = purchaseMaster.Dept;
            parms[3].Value = purchaseMaster.ContractName.Trim();
            parms[4].Value = purchaseMaster.ContractNo.Trim();
            parms[5].Value = purchaseMaster.ReceivedWh;
            parms[6].Value = purchaseMaster.Buyer.Trim();
            parms[7].Value = purchaseMaster.PayAccount.Trim();
            parms[8].Value = purchaseMaster.PayAmount;
            parms[9].Value = purchaseMaster.PayDate;
            parms[10].Value = purchaseMaster.InDate;
            parms[11].Value = purchaseMaster.EntryDate;
            parms[12].Value = purchaseMaster.EntryEmployee.Trim();
            parms[13].Value = purchaseMaster.InvoiceNo.Trim();
            parms[14].Value = purchaseMaster.InvoiceDate;
            parms[15].Value = purchaseMaster.ReferenceNo.Trim();

            parms[16].Value = purchaseMaster.Remark.Trim();
            parms[17].Value = purchaseMaster.RecordId;
            parms[17].Direction = ParameterDirection.InputOutput;
            parms[18].Value = userId.Trim();
            parms[19].Value = "";
            parms[19].Direction = ParameterDirection.InputOutput;
            parms[20].Value = 0;
            parms[20].Direction = ParameterDirection.InputOutput;

            return parms;
        }

        private SqlParameter[] SetPurchaseDetailParameters(PurchaseDetailInfo purchaseDetail, string userId)
        {
            SqlParameter[] parms = {
                new SqlParameter("@PurchaseNo", SqlDbType.VarChar, 30),
                new SqlParameter("@LineNo", SqlDbType.VarChar, 3), 
                new SqlParameter("@ProductNo", SqlDbType.VarChar, 10),
                new SqlParameter("@LotNo", SqlDbType.VarChar, 20),
                new SqlParameter("@ProductDate", SqlDbType.DateTime), 
                new SqlParameter("@Qty", SqlDbType.Decimal, 10), 
                new SqlParameter("@Price", SqlDbType.Decimal, 10), 
                new SqlParameter("@Amount", SqlDbType.Decimal, 15), 
                new SqlParameter("@DiscountRate", SqlDbType.Decimal, 15),
                new SqlParameter("@DiscountPrice", SqlDbType.Decimal, 10), 
                new SqlParameter("@DiscountAmt", SqlDbType.Decimal, 15), 
                new SqlParameter("@TaxRate", SqlDbType.Decimal, 10), 
                new SqlParameter("@TaxAmount", SqlDbType.Decimal, 15),
                new SqlParameter("@PriceInTax", SqlDbType.Decimal, 10),
                new SqlParameter("@AmountInTax", SqlDbType.Decimal, 15),
                new SqlParameter("@ValidDate", SqlDbType.DateTime),
                new SqlParameter("@Remark", SqlDbType.VarChar, 60),
                new SqlParameter("@RecordId", SqlDbType.Int),
                new SqlParameter("@UserId", SqlDbType.VarChar, 15), 
                new SqlParameter("@ErrMsg", SqlDbType.VarChar, 100),
                new SqlParameter("@RFlag", SqlDbType.SmallInt)};

            parms[0].Value = purchaseDetail.PurchaseNo.Trim();
            parms[1].Value = purchaseDetail.LineNo.Trim();
            parms[2].Value = purchaseDetail.ProductNo.Trim();
            parms[3].Value = purchaseDetail.LotNo.Trim();
            parms[4].Value = purchaseDetail.ProductDate;
            parms[5].Value = purchaseDetail.Qty;
            parms[6].Value = purchaseDetail.Price;
            parms[7].Value = purchaseDetail.Amount;
            parms[8].Value = purchaseDetail.DiscountRate;
            parms[9].Value = purchaseDetail.DiscountPrice;
            parms[10].Value = purchaseDetail.DiscountAmt;
            parms[11].Value = purchaseDetail.TaxRate;
            parms[12].Value = purchaseDetail.TaxAmount;
            parms[13].Value = purchaseDetail.PriceInTax;
            parms[14].Value = purchaseDetail.AmountInTax;
            parms[15].Value = purchaseDetail.ValidDate;
            parms[16].Value = purchaseDetail.Remark.Trim();
            parms[17].Value = purchaseDetail.RecordId;
            parms[17].Direction = ParameterDirection.InputOutput;
            parms[18].Value = userId.Trim();
            parms[19].Value = "";
            parms[19].Direction = ParameterDirection.InputOutput;
            parms[20].Value = 0;
            parms[20].Direction = ParameterDirection.InputOutput;

            return parms;
        }
    }
}
